![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
IndexesAn index is an optional structure designed to help you achieve faster access to your data. Just like the index in this book, an Oracle index is logically and physically independent of the data in the associated table or cluster. You can use the index to speed access to the data, or you can retrieve the data independently from the index by searching the tables for it. When optimally configured and used, indexes can significantly reduce I/O to the data files and greatly improve performance. The presence of an index is transparent to the user or application and requires no application changes. However, if you know of the existence of the index and design the application to take advantage of the index, you can greatly reduce the I/Os necessary to retrieve the desired data. The only indication of an index may be improved time to access the data. Once an index has been created on a table, the maintenance of that index is done automatically by Oracle. Inserts, updates, and deletions of rows are automatically updated in the related indexes. A table can have any number of indexes, but the more indexes there are, the more overhead is incurred during table updates, inserts, and deletions. This overhead is incurred because all associated indexes must be updated whenever table data is altered.
Index Types There are several different types of indexes:
A composite index is useful when SELECT statements have WHERE clauses that reference several values in the table. Because the index is accessed based on the order of the columns used in the definition, it is wise to base the index order on the frequency of use. The most referenced column should be defined first, and so on. The index should be created based on the values accessed in the application; the application should be developed to take advantage of the indexes. Having knowledge of and influence over these indexes can be very useful to the application developer. What To Index The index is usually determined by the column values that are indexed. Remember that the more indexes on a table, the more overhead is incurred during updates, inserts, and deletes. It is important to index selectively. Which Tables Should Be Indexed? Use the following guidelines to decide which tables to index:
Once you decide to use an index, you must then decide which columns to put the index on. You may index one or more columns, depending on the table.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |